import numpy as np
import pandas as pd
import xlrd
from matplotlib.pyplot import axis

students = pd.read_excel('./doc/六年级上学期第二次月考成绩单.xlsx', sheet_name='年级成绩',dtype={'班级':str},skiprows=1)
students = students[['班级','姓名','生物','名次']]
students['班级平均分'] = np.nan
# stu1 = students[students['班级']== 6.1]
# sw_mean1 =stu1['生物'].mean()
students['班级'].loc[students['班级']=='1班'] = 1
students['班级'].loc[students['班级']=='2班'] = 2
students['班级'].loc[students['班级']=='3班'] = 3
students['班级'].loc[students['班级']=='4班'] = 4
students['班级'].loc[students['班级']=='5班'] = 5
students['班级'].loc[students['班级']=='6班'] = 6
students.rename(columns={'名次': '年级名次'}, inplace=True)
students['年级名次'] = students['年级名次'].astype('int')
students['班级平均分'].loc[students['班级'] == 1] = students[students['班级']== 1]['生物'].mean()
students['班级平均分'].loc[students['班级'] == 2] = students[students['班级']== 2]['生物'].mean()    
students['班级平均分'].loc[students['班级'] == 3] = students[students['班级']== 3]['生物'].mean()
students['班级平均分'].loc[students['班级'] == 4] = students[students['班级']== 4]['生物'].mean()
students['班级平均分'].loc[students['班级'] == 5] = students[students['班级']== 5]['生物'].mean()
students['班级平均分'].loc[students['班级'] == 6] = students[students['班级']== 6]['生物'].mean()
sw_mean_all=students['生物'].mean()
students['年级平均分']  = np.repeat(sw_mean_all,len(students))
students['考试时间']  = np.repeat(202112,len(students))
print(students)
students.to_excel('./doc/六年级上学期第二次月考成绩单_202112.xlsx')

